# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
from datetime import datetime, timedelta

# 加载数据
file_path = r'data/16/客户分类-原始数据(1).xlsx'
df = pd.read_excel(file_path)

# 确保日期列是datetime类型
df['订单创建时间'] = pd.to_datetime(df['订单创建时间'])

# 定义分析的时间范围
start_date = datetime(2021, 10, 1)
end_date = datetime(2022, 3, 31)

# 筛选出指定时间段内的交易记录，并且订单状态为已完成的记录
df_filtered = df[(df['订单创建时间'] >= start_date) & 
                 (df['订单创建时间'] <= end_date) & 
                 (df['订单状态'] == '交易成功')]

# 计算每个客户的购买频率
purchase_counts = df_filtered.groupby('买家会员名')['订单创建时间'].count().reset_index()
purchase_counts.columns = ['买家会员名', '购买次数']

# 定义分类函数
def classify_customer(purchase_count):
    if purchase_count == 1:
        return '普通客户'
    elif 1 < purchase_count <= 3:
        return '普通会员客户'
    elif 3 < purchase_count <= 5:
        return '银会员客户'
    elif 5 < purchase_count <= 8:
        return '金会员客户'
    else:
        return '超级会员客户'

# 应用分类函数并添加分类结果到DataFrame
purchase_counts['客户类别'] = purchase_counts['购买次数'].apply(classify_customer)

# 统计每个类别的客户数量
category_counts = purchase_counts['客户类别'].value_counts()

# 找出金会员中购买次数为8次的客户
golden_customers_with_8_purchases = purchase_counts[
    (purchase_counts['客户类别'] == '金会员客户') & (purchase_counts['购买次数'] == 8)
]['买家会员名'].tolist()

# 找出超级会员中购买次数最高的客户及其购买次数
super_customers = purchase_counts[purchase_counts['客户类别'] == '超级会员客户']
if not super_customers.empty:
    max_purchase_super_customer = super_customers.loc[super_customers['购买次数'].idxmax()]
    max_purchase_super_customer_name = max_purchase_super_customer['买家会员名']
    max_purchase_super_customer_count = max_purchase_super_customer['购买次数']
else:
    max_purchase_super_customer_name = "无"
    max_purchase_super_customer_count = 0

# 输出结果
print(f"1. 属于普通客户的共有 {category_counts.get('普通客户', 0)} 人。")
print(f"2. 属于普通会员客户的共有 {category_counts.get('普通会员客户', 0)} 人。")
print(f"3. 属于银会员客户的共有 {category_counts.get('银会员客户', 0)} 人。")
print(f"4. 属于金会员客户的共有 {category_counts.get('金会员客户', 0)} 人，其中产生了8次购买行为的客户是：{', '.join(golden_customers_with_8_purchases) or '无'}。")
print(f"5. 属于超级会员客户的共有 {category_counts.get('超级会员客户', 0)} 人，其中购买次数最高的客户是 {max_purchase_super_customer_name}，其购买次数是 {int(max_purchase_super_customer_count)} 次。")